package com.dao.impl;

import com.dao.DBUtils;
import com.dao.EmployeeDao;
import com.model.Employee;
import com.model.EmployeeView;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl extends DBUtils implements EmployeeDao {
    @Override
    public void addEmplyee(Employee employee) {
        String sql = "insert into tbl_employee values(default,?,?,?,?)";
        super.executeUpdate(sql,employee.getEmp_name(),
                employee.getEmp_sex(),
                employee.getEmp_birthday(),
                employee.getEmp_salary());

    }

    @Override
    public void updateEmplyeeSalary(String empName, Float salary) {
        String sql = "update tbl_employee set emp_salary=emp_salary+? where emp_name=?";
        super.executeUpdate(sql,salary,empName);
    }

    @Override
    public List<EmployeeView> queryEmployee() {
        String sql = "select emp_id,emp_name,CASE WHEN emp_sex=1 THEN '男' WHEN emp_sex=0 THEN '女' END as 'emp_sex',TIMESTAMPDIFF(YEAR,emp_birthday,now()) as 'emp_age',emp_salary from tbl_employee";
        try {
            super.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List<EmployeeView> employeeViews= new ArrayList<EmployeeView>();
            while(rs.next()){
                EmployeeView employeeView = new EmployeeView();

                employeeView.setEmp_id(rs.getInt(1));
                employeeView.setEmp_name(rs.getString(2));
                employeeView.setEmp_sex(rs.getString(3));
                employeeView.setEmp_age(rs.getInt(4));
                employeeView.setEmp_salary(rs.getFloat(5));
                employeeViews.add(employeeView);
            }
            return employeeViews;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.closeAll();
        }
        return null;
    }

    @Override
    public List<EmployeeView> queryEmployeeOrder() {
        String sql = "select emp_id,emp_name,CASE WHEN emp_sex=1 THEN '男' WHEN emp_sex=0 THEN '女' END as 'emp_sex',TIMESTAMPDIFF(YEAR,emp_birthday,now()) as 'emp_age',emp_salary from tbl_employee order by emp_salary desc limit 0,3";
        try {
            super.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List<EmployeeView> employeeViews= new ArrayList<EmployeeView>();
            while(rs.next()){
                EmployeeView employeeView = new EmployeeView();

                employeeView.setEmp_id(rs.getInt(1));
                employeeView.setEmp_name(rs.getString(2));
                employeeView.setEmp_sex(rs.getString(3));
                employeeView.setEmp_age(rs.getInt(4));
                employeeView.setEmp_salary(rs.getFloat(5));
                employeeViews.add(employeeView);
            }
            return employeeViews;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.closeAll();
        }
        return null;
    }
}
